Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Triggers

Another way to ensure and verify data integrity is with the use of triggers. A trigger is a procedure stored in the database; it is fired whenever a table is modified. Triggers can be used to enforce integrity constraints and database security.

A trigger is created and defined to fire either before or after a modification to the table has occurred. To create a trigger, use the Oracle CREATE TRIGGER command. By creating triggers on database objects, you can further ensure data integrity.

Although triggers are typically used to enforce referential integrity, there are other uses for triggers:

  Auditing. Triggers can be used to create audit information for certain types of transactions or table accesses.
  Data validation. By using triggers, you can perform additional data validation to prevent invalid data from being inserted into the database.
  Security. Triggers can be used to enforce security rules.
  Data generation. Data made up of other data can be generated with triggers.
  Enforce business rules. Complex business rules can be enforced by using triggers.

Using Triggers

The preceding list gave several uses of triggers. The following list shows a few ways triggers can be used in applications:

  Event logging. Triggers can be used to automatically log accesses to certain tables in the database. By using triggers to log accesses on a table basis, you can individually monitor each table and retrieve the results of such monitoring. Remember that although Oracle offers auditing features with the AUDIT command, if your needs are very specific and limited to certain tables, triggers can be more efficient.
  Data validation. Triggers can be useful for data validation when the use of referential integrity constraints are not sufficient. The use of triggers can ensure that invalid transactions are not executed on the database.
  Data generation. Triggers can also be used to generate derived data. By firing a trigger on certain input data, other data derived from that input data can be automatically generated. Triggers can facilitate the creation of the data and can provide performance enhancements over generating this data in the application. Performance is enhanced because network traffic can be reduced as can the overhead involved in issuing additional SQL statements necessary to perform the same action.

Using Alerts

When using triggers, you can take advantage of the RDBMS_ALERT package supplied by Oracle. With this package, you can enable the use of asynchronous notification of database events. With the DBMS_ALERT package, applications can be signaled if an event occurs in the database—a useful function for applications that need an event to occur before proceeding or for applications that signal error conditions that have occurred.

Waiting for events to occur asynchronously is much more efficient than polling the database to see whether the event has occurred. When you poll, you generate network traffic and execute SQL statements unnecessarily. If your application must wait for an event, using the RDBMS_ALERT package is the way to do it.

Creating Triggers

Triggers are created in a manner almost identical to stored procedures. The syntax for a trigger begins with these keywords:

CREATE OR REPLACE TRIGGER trigger_name

These keywords are followed by the WHEN clause, which determines when the trigger is fired. The WHEN clause can consist of these keywords:

BEFORE or AFTER

Following these keywords are the qualifying statement(s):

DELETE or INSERT or UPDATE

The qualifying statements are followed by this clause:

ON table_name

In the case of an UPDATE, you can specify column names.

The addition of this statement specifies that the trigger will fire for each row that is accessed, rather than on a statement basis:

FOR EACH ROW

Here is an example of a trigger that uses the familiar DOGS table:

SQL> CREATE OR REPLACE TRIGGER old_one
  2    AFTER INSERT OR UPDATE OR DELETE ON dogs
  3    FOR EACH ROW
  4   BEGIN
  5      IF (:new.age > 8) THEN
  6        RDBMS_OUTPUT.PUT_LINE('Note: Dog is older than 8 years.');
  7     END IF;
  8  END;
  9  /

Trigger created.

Now if you insert a dog into the DOGS table that is older than 8 years old, you get the following output:

SQL> INSERT INTO dogs
  2  ( dogname, age, breed, owner )
 3  VALUES
   4  ( 'Molly', 14, 5, 'Hanks' );
Note: Dog is older than 8 years.

1 row created.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.